
//
//  PublicDatabaseEngine.m
//  GrowUP
//
//  Created by Hobo.Chen on 7/9/11.
//  Copyright 2011 __MyCompanyName__. All rights reserved.
//


#import "PublicDatabaseEngine.h"
#import "CJSONDeserializer.h"

@implementation PublicDatabaseEngine


static PublicDatabaseEngine *_shareEngine = nil;

+ (PublicDatabaseEngine *)sharedDatabaseEngine{
	if (!_shareEngine) {
		_shareEngine = [[PublicDatabaseEngine alloc] init];
	}
	
	return _shareEngine;
}


#pragma mark -
#pragma mark sql

-(NSString *)selectJsonStringWithSQL:(NSString *)sql{
    
       
    //sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
    
    NSString *myContent;
    myContent = nil;
    
    if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
		BOOL isFirst = YES;
        while(sqlite3_step(statement) == SQLITE_ROW) {
			//set array
            if(isFirst){
                char *myChar =(char*) sqlite3_column_text(statement,1);
                // CLog(@"myChar %@", myChar);
                
                if (myChar != nil) {
                    myContent = [[NSString alloc] initWithUTF8String:myChar];
                  
                }
                
                isFirst=NO;
            }
            
            //				int  _id = sqlite3_column_int(statement, 0);
            //                                
            //            [myArray addObject:[NSNumber numberWithInt:_id]];
            
			
		}
		
	}
	
	sqlite3_close(database);
   	
	return myContent;

    
    
    
}

- (NSString *)selectBapooAdeviseStringWithSQL:(NSString *)sql{
    
    
    //sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
    
    NSString *myContent;
    myContent = nil;
    
    if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
		BOOL isFirst = YES;
        while(sqlite3_step(statement) == SQLITE_ROW) {
			//set array
            if(isFirst){
                char *myChar =(char*) sqlite3_column_text(statement,7);
                // CLog(@"myChar %@", myChar);
                
                if (myChar != nil) {
                    myContent = [[NSString alloc] initWithUTF8String:myChar];
                    
                }
                else{
                    myContent = nil;
                }
                
                isFirst=NO;
            }
            
            //				int  _id = sqlite3_column_int(statement, 0);
            //                                
            //            [myArray addObject:[NSNumber numberWithInt:_id]];
            
			
		}
		
	}
	
	sqlite3_close(database);
   	
	return myContent;
    
    
    
    
}


-(NSString *)selectBapooResultStringWithSQL:(NSString *)sql{
    
    
    //sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
    
    NSString *myContent;
     myContent = nil;
    
    if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
		BOOL isFirst = YES;
        while(sqlite3_step(statement) == SQLITE_ROW) {
			//set array
            if(isFirst){
                char *myChar =(char*) sqlite3_column_text(statement,6);
                // CLog(@"myChar %@", myChar);
                
                if (myChar != nil) {
                    myContent = [[NSString alloc] initWithUTF8String:myChar];
                    
                }
                else{
                    myContent = nil;
                }
                
                isFirst=NO;
            }
            
            //				int  _id = sqlite3_column_int(statement, 0);
            //                                
            //            [myArray addObject:[NSNumber numberWithInt:_id]];
            
			
		}
		
	}
	
	sqlite3_close(database);
   	
	return myContent;
    
    
    
    
}

-(NSMutableArray *)selectTLRulerWithSQL:(NSString *)sql{
    
    //init array
	NSMutableArray *myArray = [[NSMutableArray alloc] init];
    
    
    //sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
    
    
    
    if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
		BOOL isFirst = YES;
        while(sqlite3_step(statement) == SQLITE_ROW) {
			//set array
            if(isFirst){
                char *myChar =(char*) sqlite3_column_text(statement,1);
                // CLog(@"myChar %@", myChar);
                
                if (myChar != nil) {
                    NSString *myContent = [[NSString alloc] initWithUTF8String:myChar];
                    NSString *content=[NSString stringWithFormat:@"{\"object\":%@}",myContent];
                    NSError *error = nil;
                    NSData *dat=[content dataUsingEncoding: NSUTF8StringEncoding];
                    NSDictionary *dict = [[CJSONDeserializer deserializer] deserializeAsDictionary:dat error:&error];
                    [myArray addObjectsFromArray:[dict objectForKey:@"object"]];
                    
                    [myContent release];
                    
                }
                
                isFirst=NO;
            }
            
            //				int  _id = sqlite3_column_int(statement, 0);
            //                                
            //            [myArray addObject:[NSNumber numberWithInt:_id]];
            
			
		}
		
	}
	
	sqlite3_close(database);
   	
	return myArray;
	
    
}




-(NSDictionary *)selectJsonDataWithSQL:(NSString *)sql{
    //sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
    
    NSDictionary *dict = nil;
    
    if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
		//如果将while改为 if 则会出现database 锁死问题，所以使用while加个bool来做
		BOOL isFirst = YES;
		while(sqlite3_step(statement) == SQLITE_ROW) {
			//set array
			if (isFirst) {
				//init dict
				dict = [[NSMutableDictionary alloc] init];
				//int  _id = sqlite3_column_int(statement, 0);
                char *myChar =(char*) sqlite3_column_text(statement,1);
                
                
                if (myChar != nil) {
					NSString *myContent = [[NSString alloc] initWithUTF8String:myChar];
					NSError *error = nil;
                    NSData *dat=[myContent dataUsingEncoding: NSUTF8StringEncoding];
                    dict = [[CJSONDeserializer deserializer] deserializeAsDictionary:dat error:&error];
                    
					[myContent release];
				}
                
                
				isFirst = NO;
			}
			
			
		}
		
	}
	
	sqlite3_close(database);
	
    return dict;
    
    
}



-(NSMutableArray *)selectJsonArrayWithSQL:(NSString *)sql{
    //sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
    
    NSMutableArray *mutableArray = nil;
    
    if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
		//如果将while改为 if 则会出现database 锁死问题，所以使用while加个bool来做
		while(sqlite3_step(statement) == SQLITE_ROW) {
			//set array
			//init dict
            CLog(@"Test 1");
            
            if (mutableArray == nil) {
                mutableArray = [[NSMutableArray alloc] init];
            }
            
            //int  _id = sqlite3_column_int(statement, 0);
            char *myChar =(char*) sqlite3_column_text(statement,1);
            
            CLog(@"Test 2");
            
            if (myChar != nil) {
                NSString *myContent = [[NSString alloc] initWithUTF8String:myChar];
                NSError *error = nil;
                NSData *dat=[myContent dataUsingEncoding: NSUTF8StringEncoding];
                
                NSMutableDictionary *dict = [[NSMutableDictionary alloc] init];
                
                dict = [[CJSONDeserializer deserializer] deserializeAsDictionary:dat error:&error];

                
                [mutableArray addObject:dict];
                CLog(@"Test 3");
                [myContent release];
                CLog(@"Test 4");
            }
		}
		
	}
    
    CLog(@"Test 5");
	
	sqlite3_close(database);
    
    
    CLog(@"mutableArray %@", mutableArray);
	
    return mutableArray;
    
}


- (NSMutableDictionary *)selectOneWithSQL:(NSString *)sql  select:(NSArray *)array
{
	NSMutableDictionary *dict = nil;
	
	//sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
	
	if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
		//如果将while改为 if 则会出现database 锁死问题，所以使用while加个bool来做
		BOOL isFirst = YES;
		while(sqlite3_step(statement) == SQLITE_ROW) {
			//set array
			if (isFirst) {
				//init dict
				dict = [[NSMutableDictionary alloc] init];
				
				int count = [array count];
				
				for (int i=0; i<count; i++) {
					char *myChar = (char *)sqlite3_column_text(statement, i);
					
					////NSLog(@"myChar %s ",myChar);
					
					if (myChar != nil) {
						NSString *myContent = [[NSString alloc] initWithUTF8String:myChar];
						NSString *myKey = [array objectAtIndex:i];
						
						if (myContent!=nil) {
							[dict setObject:myContent forKey:myKey];
						}
						
						////NSLog(@"content=%@; key=%@; i=%d",myContent,myKey,i);
						
						[myContent release];
					}
					
				}
				
				isFirst = NO;
			}
			
			
		}
		
	}
	
	sqlite3_close(database);
	
	
	return dict;
}



- (NSArray *)searchWithSQL:(NSString *)sql  select:(NSArray *)array{
	//init array
	NSMutableArray *myArray = [[NSMutableArray alloc] init];
	
	//sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
	
	if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
		
		while (sqlite3_step(statement) == SQLITE_ROW) {
			//set array
			
			//init dict
			NSMutableDictionary *dict = [[NSMutableDictionary alloc] init];
			
			int count = [array count];
			
			for (int i=0; i<count; i++) {
				char *myChar = (char *)sqlite3_column_text(statement, i);
				
				////NSLog(@"myChar %s ",myChar);
				
				if (myChar != nil) {
					NSString *myContent = [[NSString alloc] initWithUTF8String:myChar];
					NSString *myKey = [array objectAtIndex:i];
					
					if (myContent!=nil) {
						[dict setObject:myContent forKey:myKey];
					}
					
					////NSLog(@"content=%@; key=%@; i=%d",myContent,myKey,i);
					
					[myContent release];
				}
				
			}
			
			[myArray addObject:dict];
			[dict release];
			
		}
		
	}
	
	sqlite3_close(database);
	
	NSArray *finalArray = [[NSArray alloc] initWithArray:myArray];
	[myArray release];
	
	return finalArray;
}


- (NSArray *)searchBlobWithSQL:(NSString *)sql{
	//init array
	NSMutableArray *myArray = [[NSMutableArray alloc] init];
	
	//sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
	
	if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
		while (sqlite3_step(statement) == SQLITE_ROW) {
			//set array
			
			NSData *dataForCached = [[NSData alloc] initWithBytes:sqlite3_column_blob(statement, 0) 
														   length: sqlite3_column_bytes(statement, 0)];
			
			////NSLog(@"data %@",dataForCached);
			
			NSString *newString = [[NSString alloc] initWithData:dataForCached encoding:NSUTF8StringEncoding];
			if (newString==nil) {
				newString = @"";
			}
			[myArray addObject:newString];
			
			////NSLog(@"blob %@",newString);
		}
		
	}
	
	sqlite3_close(database);
	
	NSArray *finalArray = [[NSArray alloc] initWithArray:myArray];
	[myArray release];
	
	return finalArray;
}


- (BOOL)isValueExitWithSQl:(NSString *)sql{
    
    //sql
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"Failed to open database");
	}
	
	NSString *query = sql;
	sqlite3_stmt *statement;
    
    
    
    if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
		return YES;		
	}
    else{
        return NO;
    }
	
	sqlite3_close(database);
   	
    
    
}

- (BOOL)executeSQL:(NSString *)sql{
	char * errorMsg;
	
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		//NSLog(@"DB can't open");
		sqlite3_close(database);
		return NO;
	}
	
	if (sqlite3_exec (database, [sql UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK)
	{
		//NSLog(@"insert err: %s",errorMsg);
		sqlite3_close(database);
		return NO;	
	}
	
	//NSLog(@"insert or replace successfully");
	sqlite3_close(database);
	
	return YES;
}

- (int)insertOrReplaceWithSQL:(NSString *)sql{
	char * errorMsg;
	
	if (sqlite3_open([[self dataFilePath:kDatabaseName] UTF8String], &database) != SQLITE_OK) {
		CLog(@"DB can't open");
		sqlite3_close(database);
		return 0;
	}
	
	if (sqlite3_exec (database, [sql UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK)
	{
		CLog(@"insert err: %s",errorMsg);
		sqlite3_close(database);
		return 0;	
	}
	
	int lastID = sqlite3_last_insert_rowid(database);
	CLog(@"insert last id %d",lastID);
	
	sqlite3_close(database);
	
	return lastID;
}

- (NSString *)dataFilePath:(NSString *)filename{
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDirectory = [paths objectAtIndex:0];
	return [documentsDirectory stringByAppendingPathComponent:filename];
}

@end
